drop table jms_dws.dws_leave_message_regist_agg;
CREATE TABLE jms_dws.dws_leave_message_regist_agg (
                                                      dt                     DATE COMMENT '日期',
                                                      leave_message_type             int comment '留言类型:1、破损 2、延误 3、遗失 4、其他',
                                                      register_network_type int comment '登记网点类型:1.网点，2.集散，3.中心，4.代理区，5.总部',
                                                      receive_network_type int comment '接受网点类型:1.网点，2.集散，3.中心，4.代理区，5.总部',
                                                      register_network_code          varchar(80) comment '登记网点编码',
                                                      register_agent_code            varchar(80) comment '登记网点大区code',
                                                      register_agent_name            varchar(80) comment '登记网点大区名称',
                                                      register_financial_center_code varchar(80) comment '登记网点代理区code',
                                                      register_financial_center_name varchar(80) comment '登记网点代理区名称',
                                                      register_network_name          varchar(80) comment '登记网点名称',
                                                      receive_agent_code            varchar(80) comment '接受网点大区code',
                                                      receive_agent_name            varchar(80) comment '接受网点大区名称',
                                                      receive_financial_center_code varchar(80) comment '接受网点代理区code',
                                                      receive_financial_center_name varchar(80) comment '接受网点代理区名称',
                                                      receive_network_code          varchar(80) comment '接受网点编码',
                                                      receive_network_name          varchar(80) comment '接受网点名称',
                                                      message_cnt                    bigint comment '留言总数',
                                                      register_reply_cnt                      bigint comment '登记方已回复数',
                                                      register_unreply_cnt                    bigint comment '登记方未回复数',
                                                      register_end_cnt                        bigint comment '登记方已完结数',
                                                      register_reply_rate                     double comment '登记方回复率',
                                                      register_end_rate                       double comment '登记方完结率',
                                                      receive_reply_cnt                       bigint comment '接受方已回复数',
                                                      receive_unreply_cnt                    bigint comment '接受方未回复数',
                                                      receive_end_cnt                        bigint comment '接受方已完结数',
                                                      receive_reply_rate                     double comment '接受方回复率',
                                                      receive_end_rate                       double comment '接受方完结率'
) ENGINE=OLAP
DUPLICATE KEY(`dt`, `leave_message_type`,`register_network_type`,`receive_network_type`,`register_network_code`)
COMMENT '留言登记时间汇总'
PARTITION BY RANGE(`dt`)
(
START ("2022-04-01") END ("2022-05-26") EVERY (INTERVAL 1 day)
)
DISTRIBUTED BY HASH(`register_network_code`) BUCKETS 10
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.start" = "-180",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "10",
"in_memory" = "false",
"storage_format" = "V2"
);
